﻿using CloudDcPc.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class GzBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public GzBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        public bool DelGzById(int id)
        {
            string sql = "delete from dc_gz where id='" + id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool AddGz(Gz gz)
        {
            string sql = "insert into dc_gz(sign_name,department,phone,doer,approver) values('" + gz.Sign_name + "','" + gz.Department + "','" + gz.Phone + "','" + gz.Doer + "','" + gz.Approver + "')";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool UpdateGz(Gz gz)
        {
            string sql = "update dc_gz set sign_name='" + gz.Sign_name + "',department='" + gz.Department + "',phone='" + gz.Phone + "',doer='" + gz.Doer + "',approver='" + gz.Approver + "' where id='" + gz.Id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }

        }


        public ObservableCollection<Gz> GetAllGz()
        {
            ObservableCollection<Gz> list = new ObservableCollection<Gz>();
            Gz gz = null;
            string sql = "select * from dc_gz";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    gz = new Gz()
                    {
                        Id = (int)reader["id"],
                        Sign_name = reader["sign_name"].ToString(),
                        Department = reader["department"].ToString(),
                        Phone = reader["phone"].ToString(),
                        Doer = reader["doer"].ToString(),
                        Approver = reader["approver"].ToString(),
                    };
                    list.Add(gz);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public bool HasGz(string s,string d)
        {
            string sql = "select * from dc_gz where sign_name='"+s+"' and department='"+d+"'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }

        }

        public bool HasGzForUpdate(int id,string s, string d)
        {
            string sql = "select * from dc_gz where sign_name='"+s+"' and department='"+d+"' and id!="+id;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }

        }
    }
}
